﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using TheoriesBraun.BLL;
using System.Data.SqlClient;
using System.Data;
using System.Windows.Forms;

namespace TheoriesBraun.DAL
{
    class TheoriesDAL
    {
        public DataTable getAllTheories()
        {
            DataTable dt = null;
            try
            {
                SqlCommand cmd = new SqlCommand("SELECT * FROM Theories", DBConnect.getConnection());
                SqlDataReader reader = cmd.ExecuteReader();
                dt = new DataTable();
                dt.Load(reader);
                reader.Close();
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable loadTheoryList()
        {
            DataTable dt = null;
            try
            {
                SqlCommand cmd = new SqlCommand("SELECT (Chapter + ' - ' + NameOfChapter) as ChapterAndName FROM Theories", DBConnect.getConnection());
                SqlDataReader reader = cmd.ExecuteReader();
                dt = new DataTable();
                dt.Load(reader);
                reader.Close();
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void addTheory(TheoriesBLL theory)
        {
            try
            {
                string query = "INSERT INTO Theories VALUES (N'Chương " + theory.Chapter +
                    "', N'" + theory.Name +
                    "', N'" + theory.Description + "')";
                SqlCommand cmd = new SqlCommand(query, DBConnect.getConnection());
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        public void editTheory(TheoriesBLL theory)
        {
            try
            {
                string query = "UPDATE Theories SET NameOfChapter = N'" + theory.Name +
                    "', Description = N'" + theory.Description +
                    "' WHERE Chapter = N'" + theory.Chapter + "'";
                SqlCommand cmd = new SqlCommand(query, DBConnect.getConnection());
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
    }
}
